package com.example.demo.Dao;


import com.example.demo.packageModel.dept;     //部门
import com.example.demo.packageModel.emp;      //职工
import com.example.demo.packageModel.Together; //部门+员工


import java.math.BigDecimal;
import java.sql.*;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;


//import org.apache.ibatis.annotations.Mapper;

//@Entity
//@Table(name="stu")

//@Mapper
import org.springframework.stereotype.Component;
@Component
public class UserDao {
    //查找单个职员
    public emp getOneEmp(String EmpNo) throws ClassNotFoundException, SQLException{
        Connection connection = LogIn.getConn();
        String sql = "select * from emp where empno=\"" + EmpNo + "\"";
        emp user = null;
        Statement statement = null;

        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        String empInfo = null;
        String empno="", ename="", job="", mgr="", hiredate="", sal="", comm="", deptno="";
        while (rs.next()) {
            empno = rs.getString("empno");
            ename = rs.getString("ename");
            job = rs.getString("job");
            mgr = rs.getString("mgr");
            hiredate = rs.getString("hiredate");
            sal = rs.getString("sal");
            comm = rs.getString("comm");
            deptno = rs.getString("deptno");
        }
        System.out.println(empno+" "+ename+" "+job+" "+mgr+" "+hiredate+" "+sal+" "+comm+" "+deptno);
        emp ee = new emp();
        ee.setEmpno(Integer.parseInt( EmpNo ));
        ee.setEname(ename);
        ee.setJob(job);
        Integer empMgr;
        if (mgr == null)
            empMgr = null;
        else
            empMgr = Integer.parseInt( mgr );
        ee.setMgr(empMgr);
        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        LocalDate date = LocalDate.parse(hiredate, df);
        ee.setHiredate(date);
        BigDecimal bd0=new BigDecimal(sal);
        ee.setSal(bd0);
        BigDecimal bd1;
        if (comm == null)
            bd1 = null;
        else
            bd1= new BigDecimal(comm);
        ee.setComm(bd1);
        ee.setDeptno(Integer.parseInt( deptno ));

        statement.close();
        connection.close();

        return ee;
    }
    //查找全部职员
    public Collection<emp> getAllEmp() throws ClassNotFoundException, SQLException{
        Connection connection = LogIn.getConn();
        String sql = "select * from emp";
        Statement statement = null;
        Map<String, emp> userMap = new HashMap<String, emp>();

        try {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);

            while (rs.next()) {
                String empno = rs.getString("empno");
                String ename = rs.getString("ename");
                String job = rs.getString("job");
                String mgr = rs.getString("mgr");
                String hiredate = rs.getString("hiredate");
                String sal = rs.getString("sal");
                String comm = rs.getString("comm");
                String deptno = rs.getString("deptno");


                emp user = new emp();
                user.setEmpno(Integer.parseInt( empno ));
                user.setEname( ename );
                user.setJob( job );
                Integer empMgr;
                if (mgr == null)
                    empMgr = null;
                else
                    empMgr = Integer.parseInt( mgr );
                user.setMgr(empMgr);
                DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                LocalDate date = LocalDate.parse(hiredate, df);
                user.setHiredate( date );
                BigDecimal bd0=new BigDecimal(sal);
                user.setSal( bd0 );
                BigDecimal bd1;
                if (comm == null)
                    bd1 = null;
                else
                    bd1= new BigDecimal(comm);
                user.setComm( bd1 );
                user.setDeptno(Integer.parseInt( deptno ));
                userMap.put(empno, user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        LogIn.close(connection, statement, null, null);
        return userMap.values();
    }
    //查找单个部门
    public String getOnedept(String DeptNo) throws ClassNotFoundException, SQLException{
        Connection connection = LogIn.getConn();
        String sql = "select * from dept where num=\"" + DeptNo + "\"";
        dept user = null;
        Statement statement = null;

        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        String deptInfo = null;
        while (rs.next())
        {
            String deptno = rs.getString("deptno");
            String dname = rs.getString("dname");
            String loc = rs.getString("loc");

            System.out.println(deptno+" " +dname+" " +loc);
            deptInfo = "deptno" + "=" + deptno + ","+
                    "dname" + "=" + dname + ","+
                    "loc" + "=" + loc;
        }

        LogIn.close(connection, statement, null, null);
        if(deptInfo == null || deptInfo.length() <= 0)
        {
            deptInfo = "deptno" + "=" + "000000" + ","+"dname" + "=" + "NoNoNo";
            return deptInfo;
        }
        return deptInfo;
    }
    //查找全部部门
    public Collection<dept> getAllDept() throws ClassNotFoundException, SQLException{
        Connection connection = LogIn.getConn();
        String sql = "select * from dept";
        Statement statement = null;
        Map<String, dept> userMap = new HashMap<String, dept>();

        try {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);

            while (rs.next()) {
                String deptno = rs.getString("deptno");
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");

                dept user = new dept();
                user.setDeptno(Integer.parseInt( deptno ));
                user.setDname( dname );
                user.setLoc( loc );

                userMap.put(deptno, user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        LogIn.close(connection, statement, null, null);
        return userMap.values();
    }
    //判断职工是否存在DB中
    public static boolean addEmpOk(int empNo) throws ClassNotFoundException, SQLException{
        Connection connection = LogIn.getConn();
        String sql = "select * from emp where empno=\"" + empNo + "\"";
        Statement statement = null;
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        if (rs.next()) //查询到结果
            {
            System.out.println("已经存在");
                statement.close();
            connection.close();
            return true;
        }
        statement.close();
        connection.close();
        return false;
    }
    //判断插入职工的部门是否存在
    public static boolean addEmpDOk(int empDeNo) throws ClassNotFoundException, SQLException{
        Connection connection = LogIn.getConn();
        String sql = "select * from dept where deptno=\"" + empDeNo + "\"";
        Statement statement = null;
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        if (rs.next()){
            statement.close();
            connection.close();
            return true;
        }
        statement.close();
        connection.close();
        return false;
    }
    //新加员工
    public static boolean addEmp(emp aa) throws ClassNotFoundException, SQLException{
        Connection connection = LogIn.getConn();
        Integer  empNo= aa.getEmpno();
        String empName = aa.getEname();
        String empJob = aa.getJob();
        Integer empMgr = aa.getMgr();
        LocalDate empDate = aa.getHiredate();
        BigDecimal empSal = aa.getSal();
        BigDecimal empCom = aa.getComm();
        Integer empDeptNo = aa.getDeptno();
        System.out.println(empNo);
        if (addEmpOk(empNo))
            return false;
        if (empName == null)
            return false;
        if (addEmpDOk(empDeptNo) == false)
            return false;

        String sql;
        if(empCom == null) {
            if (empMgr == null)
                sql = "INSERT INTO emp VALUES(\"" + empNo + "\",\"" + empName + "\",\"" + empJob + "\",null,\"" + empDate + "\",\"" + empSal + "\",null,\"" + empDeptNo + "\")";
            else
                sql = "INSERT INTO emp VALUES(\"" + empNo + "\",\"" + empName + "\",\"" + empJob + "\",\"" + empMgr + "\",\"" + empDate + "\",\"" + empSal + "\",null,\"" + empDeptNo + "\")";
        }
        else
            sql = "INSERT INTO emp VALUES(\""+empNo+"\",\""+empName+"\",\""+empJob+"\",\""+empMgr+"\",\""+empDate+"\",\""+empSal+"\",\""+empCom+"\",\""+empDeptNo+"\")";
        Statement statement = null;

        System.out.println(empCom);
        System.out.println(sql);
        statement = connection.createStatement();
        statement.execute(sql);

        statement.close();
        connection.close();
        return true;
    }
    //删除员工
    public static boolean delEmp(int empNo) throws ClassNotFoundException, SQLException{
        Connection connection = LogIn.getConn();

        if (addEmpOk(empNo) == false)
            return false;

        String sql = "DELETE FROM emp WHERE empno=\""+empNo +"\"";        //String sql = "INSERT INTO emp VALUES(empNo,empName,empJob,empMgr,empDate,empSal,empCom,empDeptNo)";
        Statement statement = null;
        statement = connection.createStatement();
        statement.execute(sql);

        statement.close();
        connection.close();

        return true;
    }
    //修改
    public static boolean chaEmp(emp aa) throws ClassNotFoundException, SQLException{
        Connection connection = LogIn.getConn();
        int  empNo= aa.getEmpno();
        String empName = aa.getEname();
        String empJob = aa.getJob();
        Integer empMgr = aa.getMgr();
        LocalDate empDate = aa.getHiredate();
        BigDecimal empSal = aa.getSal();
        BigDecimal empCom = aa.getComm();
        int empDeptNo = aa.getDeptno();

        if (addEmpOk(empNo) == false)
            return false;
        if (empName == null)
            return false;
        if (addEmpDOk(empDeptNo) == false)
            return false;

        String sql;
        if(empCom == null){
            if(empMgr == null)
                sql = "UPDATE emp set ename=\""+empName+"\" ,job=\""+empJob+"\" ,mgr=null ,hiredate=\""+empDate+"\" ,sal=\""+empSal+"\" ,comm=null ,deptno=\""+empDeptNo+"\" where empno=\""+empNo+"\"";
            else
                sql ="UPDATE emp set ename=\""+empName+"\" ,job=\""+empJob+"\" ,mgr=\""+empMgr+"\" ,hiredate=\""+empDate+"\" ,sal=\""+empSal+"\" ,comm=null ,deptno=\""+empDeptNo+"\" where empno=\""+empNo+"\"";
        }
        else
            sql ="UPDATE emp set ename=\""+empName+"\" ,job=\""+empJob+"\" ,mgr=\""+empMgr+"\" ,hiredate=\""+empDate+"\" ,sal=\""+empSal+"\" ,comm=\""+empCom+"\" ,deptno=\""+empDeptNo+"\" where empno=\""+empNo+"\"";
        Statement statement = null;

        statement = connection.createStatement();
        statement.execute(sql);
        statement.close();
        connection.close();
        return true;
    }

    //联合查询
    public static Collection<Together> getInfo(int Pa, int count) throws ClassNotFoundException, SQLException {
        Connection connection = LogIn.getConn();
        String sql;
        if (Pa < 0)
        {
            sql = "select * from emp";
        }
        else
        {
            sql = "select * from emp limit ";
            sql =sql+Pa+","+count;
        }
        System.out.println(sql);
        Statement statement = null;
        Statement statement_dep = null;
        String depname= "";
        String deploc = "";
        Map<String, Together> userMap = new HashMap<String, Together>();

        try {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);

            while (rs.next()) {
                String empno = rs.getString("empno");
                System.out.println(empno);
                String ename = rs.getString("ename");
                String job = rs.getString("job");
                String mgr = rs.getString("mgr");
                String hiredate = rs.getString("hiredate");
                String sal = rs.getString("sal");
                String comm = rs.getString("comm");
                String deptno = rs.getString("deptno");


                Integer EmpNo = Integer.parseInt( empno );
                int DeptNo = Integer.parseInt( deptno );

                String sql_De = "select * from dept where deptno = \""+DeptNo+"\"";
                statement_dep = connection.createStatement();
                ResultSet rs_dep = statement_dep.executeQuery(sql_De);
                while(rs_dep.next()){
                    depname = rs_dep.getString("dname");
                    deploc = rs_dep.getString("loc");
                }

                Together user = new Together();
                user.setEmpno(EmpNo);
                user.setEname( ename );
                user.setJob( job );
                Integer empMgr;
                if (mgr == null)
                    empMgr = null;
                else
                    empMgr = Integer.parseInt( mgr );
                user.setMgr(empMgr);

                DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                LocalDate date = LocalDate.parse(hiredate, df);
                user.setHiredate( hiredate );
                BigDecimal bd0=new BigDecimal(sal);
                user.setSal( bd0 );
                BigDecimal bd1;
                if (comm == null)
                    bd1 = null;
                else
                    bd1= new BigDecimal(comm);
                user.setComm( bd1 );

                user.setDeptno(DeptNo);
                user.setDname(depname);
                user.setLoc(deploc);
                userMap.put(empno, user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        LogIn.close(connection, statement, null, null);

        connection.close();
        return userMap.values();
    }
}
